import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pathlib import Path
from typing import Dict, List, Any
from io import BytesIO
import os

class ExcelReportGenerator:
    def __init__(self, output_dir: Path):
        """
        初始化Excel报告生成器
        
        Args:
            output_dir: 输出目录路径
        """
        self.output_dir = output_dir
        
    def generate(self, analysis_result: Dict, student_name: str, student_id: str, exam_data: Dict):
        """
        生成Excel格式的学生成绩分析报告
        
        Args:
            analysis_result: 学生成绩分析结果
            student_name: 学生姓名
            student_id: 学生准考证号
            exam_data: 考试数据，包含各次考试的详细信息
        """
        # 创建输出目录
        report_dir = self.output_dir / "students_excel"
        report_dir.mkdir(parents=True, exist_ok=True)
        
        # 创建Excel文件路径
        report_path = report_dir / f"{student_name}_{student_id}.xlsx"
        
        # 创建Excel写入器，添加nan_inf_to_errors选项
        with pd.ExcelWriter(report_path, engine='xlsxwriter', engine_kwargs={'options': {'nan_inf_to_errors': True}}) as writer:
            # 生成基本信息表
            self._generate_basic_info(writer, analysis_result)
            
            # 生成总分分析表
            self._generate_total_score_analysis(writer, analysis_result, exam_data)
            
            # 生成各科目分析表
            self._generate_subject_analysis(writer, analysis_result, exam_data)
            
            # 生成排名分析表
            self._generate_ranking_analysis(writer, analysis_result, exam_data)
            
            # 生成成绩趋势图表
            self._generate_trend_charts(writer, analysis_result, exam_data)
            
            # 生成科目对比图表
            self._generate_subject_comparison(writer, analysis_result, exam_data)
            
            # 生成排名趋势图表
            self._generate_ranking_charts(writer, analysis_result, exam_data)
            
            # 设置Excel文件属性
            workbook = writer.book
            workbook.set_properties({
                'title': f'{student_name}成绩分析报告',
                'subject': '学生成绩分析',
                'author': '成绩分析系统',
                'comments': f'准考证号: {student_id}'
            })
    
    def _generate_basic_info(self, writer: pd.ExcelWriter, data: Dict):
        """生成基本信息表"""
        # 创建基本信息数据
        basic_info = pd.DataFrame([{
            '姓名': data['基本信息']['姓名'],
            '准考证号': data['基本信息']['准考证号'],
            '班级': data['基本信息'].get('班级', ''),
            '优势科目': data['科目分析'].get('优势科目', ''),
            '弱势科目': data['科目分析'].get('弱势科目', ''),
            '最稳定科目': data['科目分析'].get('最稳定科目', ''),
            '波动最大科目': data['科目分析'].get('波动最大科目', '')
        }])
        
        # 写入Excel
        basic_info.to_excel(writer, sheet_name='基本信息', index=False)
        
        # 格式化
        workbook = writer.book
        worksheet = writer.sheets['基本信息']
        
        # 设置列宽
        worksheet.set_column('A:G', 15)
        
        # 添加标题格式
        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'top',
            'fg_color': '#D7E4BC',
            'border': 1
        })
        
        # 应用标题格式
        for col_num, value in enumerate(basic_info.columns.values):
            worksheet.write(0, col_num, value, header_format)
    
    def _generate_total_score_analysis(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成总分分析表"""
        # 获取总分数据
        total_scores = data['成绩趋势']['总分走势']
        total_ranks = data['成绩趋势']['总分校次走势']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams']:
            # 如果没有考试数据，创建一个空表
            df = pd.DataFrame({'考试': [], '总分': [], '校次': []})
            df.to_excel(writer, sheet_name='总分分析', index=False)
            return
        
        # 使用按权重排序的考试数据（最近的考试在前）
        exam_infos = exam_data['exams']
            
        # 使用display_name而不是exam_type
        exam_names = [exam.get('display_name', exam.get('exam_type', f'考试{i+1}')) 
                      for i, exam in enumerate(exam_infos)]
        
        # 创建总分分析数据
        if total_scores and any(score is not None for score in total_scores):
            # 过滤掉None值
            valid_data = [(i, score, rank) for i, (score, rank) in 
                         enumerate(zip(total_scores, total_ranks)) 
                         if score is not None]
            
            if not valid_data:
                # 如果没有有效分数，创建一个空表
                df = pd.DataFrame({'考试': [], '总分': [], '校次': []})
                df.to_excel(writer, sheet_name='总分分析', index=False)
                return
            
            valid_indices = [i for i, _, _ in valid_data]
            valid_scores = [score for _, score, _ in valid_data]
            valid_ranks = [rank for _, _, rank in valid_data]
            valid_exam_names = [exam_names[i] for i in valid_indices]
            
            total_analysis = {
                '考试': valid_exam_names,
                '总分': valid_scores,
                '校次': valid_ranks
            }
            
            # 添加统计数据
            if valid_scores:
                total_analysis['最高分'] = [max(valid_scores)] * len(valid_scores)
                total_analysis['最低分'] = [min(valid_scores)] * len(valid_scores)
                total_analysis['平均分'] = [sum(valid_scores) / len(valid_scores)] * len(valid_scores)
            
            # 计算环比变化
            score_changes = []
            for i in range(len(valid_scores)):
                if i == len(valid_scores) - 1:  # 最早的考试（列表中的最后一个）
                    score_changes.append(0)
                else:
                    # 当前成绩减去上一次成绩
                    change = valid_scores[i] - valid_scores[i+1]
                    score_changes.append(change)
            
            # 计算排名环比变化（排名下降为正，上升为负）
            rank_changes = []
            for i in range(len(valid_ranks)):
                if i == len(valid_ranks) - 1:  # 最早的考试（列表中的最后一个）
                    rank_changes.append(0)
                elif valid_ranks[i] is not None and valid_ranks[i+1] is not None:
                    # 当前排名减去上一次排名
                    change = valid_ranks[i+1] - valid_ranks[i]
                    rank_changes.append(change)
                else:
                    rank_changes.append(None)
            
            total_analysis['分数环比变化'] = score_changes
            total_analysis['排名环比变化'] = rank_changes
            
            # 创建DataFrame
            df = pd.DataFrame(total_analysis)
            
            # 写入Excel
            df.to_excel(writer, sheet_name='总分分析', index=False)
            
            # 格式化
            workbook = writer.book
            worksheet = writer.sheets['总分分析']
            
            # 设置列宽
            worksheet.set_column('A:G', 12)
            
            # 添加标题格式
            header_format = workbook.add_format({
                'bold': True,
                'text_wrap': True,
                'valign': 'top',
                'fg_color': '#D7E4BC',
                'border': 1
            })
            
            # 应用标题格式
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)
            
            # 添加条件格式（分数环比变化）
            positive_format = workbook.add_format({'font_color': 'green'})
            negative_format = workbook.add_format({'font_color': 'red'})
            
            # 应用条件格式到分数环比变化
            score_change_col = df.columns.get_loc('分数环比变化')
            worksheet.conditional_format(1, score_change_col, len(valid_scores), score_change_col, {
                'type': 'cell',
                'criteria': '>',
                'value': 0,
                'format': positive_format
            })
            
            worksheet.conditional_format(1, score_change_col, len(valid_scores), score_change_col, {
                'type': 'cell',
                'criteria': '<',
                'value': 0,
                'format': negative_format
            })
            
            # 应用条件格式到排名环比变化（排名上升为好，所以正值用绿色）
            rank_change_col = df.columns.get_loc('排名环比变化')
            worksheet.conditional_format(1, rank_change_col, len(valid_ranks), rank_change_col, {
                'type': 'cell',
                'criteria': '>',
                'value': 0,
                'format': positive_format
            })
            
            worksheet.conditional_format(1, rank_change_col, len(valid_ranks), rank_change_col, {
                'type': 'cell',
                'criteria': '<',
                'value': 0,
                'format': negative_format
            })
        else:
            # 如果没有总分数据，创建一个空表
            df = pd.DataFrame({'考试': [], '总分': [], '校次': []})
            df.to_excel(writer, sheet_name='总分分析', index=False)
    
    def _generate_subject_analysis(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成各科目分析表"""
        # 获取各科目数据
        subjects_data = data['成绩趋势']['各科走势']
        subjects_rank_data = data['成绩趋势']['各科校次走势']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams']:
            return
        
        # 使用按权重排序的考试数据（最近的考试在前）
        exam_infos = exam_data['exams']
            
        # 使用display_name而不是exam_type
        exam_names = [exam.get('display_name', exam.get('exam_type', f'考试{i+1}')) 
                      for i, exam in enumerate(exam_infos)]
        
        # 为每个科目创建一个表
        for subject, scores in subjects_data.items():
            if not scores or not any(score is not None for score in scores):
                continue
            
            # 获取科目排名数据
            ranks = subjects_rank_data.get(subject, [None] * len(scores))
            
            # 过滤掉None值
            valid_data = [(i, score, rank) for i, (score, rank) in 
                         enumerate(zip(scores, ranks)) 
                         if score is not None]
            
            if not valid_data:
                continue
            
            valid_indices = [i for i, _, _ in valid_data]
            valid_scores = [score for _, score, _ in valid_data]
            valid_ranks = [rank for _, _, rank in valid_data]
            valid_exam_names = [exam_names[i] for i in valid_indices]
            
            # 创建科目分析数据
            subject_analysis = {
                '考试': valid_exam_names,
                f'{subject}分数': valid_scores,
                f'{subject}校次': valid_ranks
            }
            
            # 计算环比变化
            score_changes = []
            for i in range(len(valid_scores)):
                if i == len(valid_scores) - 1:  # 最早的考试（列表中的最后一个）
                    score_changes.append(0)
                else:
                    # 当前成绩减去上一次成绩
                    change = valid_scores[i] - valid_scores[i+1]
                    score_changes.append(change)
            
            # 计算排名环比变化（排名下降为正，上升为负）
            rank_changes = []
            for i in range(len(valid_ranks)):
                if i == len(valid_ranks) - 1:  # 最早的考试（列表中的最后一个）
                    rank_changes.append(0)
                elif valid_ranks[i] is not None and valid_ranks[i+1] is not None:
                    # 当前排名减去上一次排名
                    change = valid_ranks[i+1] - valid_ranks[i]
                    rank_changes.append(change)
                else:
                    rank_changes.append(None)
            
            subject_analysis['分数环比变化'] = score_changes
            subject_analysis['排名环比变化'] = rank_changes
            
            # 创建DataFrame
            df = pd.DataFrame(subject_analysis)
            
            # 写入Excel
            df.to_excel(writer, sheet_name=f'{subject}分析', index=False)
            
            # 格式化
            workbook = writer.book
            worksheet = writer.sheets[f'{subject}分析']
            
            # 设置列宽
            worksheet.set_column('A:E', 12)
            
            # 添加标题格式
            header_format = workbook.add_format({
                'bold': True,
                'text_wrap': True,
                'valign': 'top',
                'fg_color': '#D7E4BC',
                'border': 1
            })
            
            # 应用标题格式
            for col_num, value in enumerate(df.columns.values):
                worksheet.write(0, col_num, value, header_format)
            
            # 添加条件格式（分数环比变化）
            positive_format = workbook.add_format({'font_color': 'green'})
            negative_format = workbook.add_format({'font_color': 'red'})
            
            # 应用条件格式到分数环比变化
            score_change_col = df.columns.get_loc('分数环比变化')
            worksheet.conditional_format(1, score_change_col, len(valid_scores), score_change_col, {
                'type': 'cell',
                'criteria': '>',
                'value': 0,
                'format': positive_format
            })
            
            worksheet.conditional_format(1, score_change_col, len(valid_scores), score_change_col, {
                'type': 'cell',
                'criteria': '<',
                'value': 0,
                'format': negative_format
            })
            
            # 应用条件格式到排名环比变化（排名上升为好，所以正值用绿色）
            if '排名环比变化' in df.columns:
                rank_change_col = df.columns.get_loc('排名环比变化')
                worksheet.conditional_format(1, rank_change_col, len(valid_ranks), rank_change_col, {
                    'type': 'cell',
                    'criteria': '>',
                    'value': 0,
                    'format': positive_format
                })
                
                worksheet.conditional_format(1, rank_change_col, len(valid_ranks), rank_change_col, {
                    'type': 'cell',
                    'criteria': '<',
                    'value': 0,
                    'format': negative_format
                })
            
            # 添加统计信息
            stats_data = [
                ['最高分', max(valid_scores)],
                ['最低分', min(valid_scores)],
                ['平均分', sum(valid_scores) / len(valid_scores)],
                ['标准差', np.std(valid_scores)]
            ]
            
            # 写入统计信息
            for i, (label, value) in enumerate(stats_data):
                worksheet.write(i+1, 6, label)
                worksheet.write(i+1, 7, value)
    
    def _generate_ranking_analysis(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成排名分析表"""
        # 获取排名数据
        rankings = data['排名分析']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams'] or not rankings:
            # 如果没有考试数据或排名数据，创建一个空表
            df = pd.DataFrame({'考试': []})
            df.to_excel(writer, sheet_name='排名分析', index=False)
            return
            
        exam_names = [exam.get('exam_type', f'考试{i+1}') for i, exam in enumerate(exam_data['exams'])]
        
        # 创建排名分析数据
        ranking_data = {'考试': exam_names}
        
        for rank_type, rank_data in rankings.items():
            if rank_type not in ['校次', '班次'] or 'data' not in rank_data:
                continue
                
            ranks = rank_data.get('数据', [])
            
            # 过滤掉None值
            valid_ranks = [(i, rank) for i, rank in enumerate(ranks) if rank is not None]
            
            if not valid_ranks:
                continue
                
            valid_indices = [i for i, _ in valid_ranks]
            valid_ranks = [rank for _, rank in valid_ranks]
            valid_exam_names = [exam_names[i] for i in valid_indices]
            
            # 更新考试名称
            if 'exam_names' not in ranking_data:
                ranking_data = {'考试': valid_exam_names}
            
            ranking_data[rank_type] = valid_ranks
            
            # 计算环比变化（排名下降为正，上升为负）
            changes = []
            for i in range(len(valid_ranks)):
                if i == len(valid_ranks) - 1:  # 最早的考试（列表中的最后一个）
                    changes.append(0)
                else:
                    change = valid_ranks[i+1] - valid_ranks[i]
                    changes.append(change)
            
            ranking_data[f'{rank_type}环比变化'] = changes
        
        # 如果没有有效的排名数据，创建一个空表
        if len(ranking_data) <= 1:
            df = pd.DataFrame({'考试': []})
            df.to_excel(writer, sheet_name='排名分析', index=False)
            return
            
        # 创建DataFrame
        df = pd.DataFrame(ranking_data)
        
        # 写入Excel
        df.to_excel(writer, sheet_name='排名分析', index=False)
        
        # 格式化
        workbook = writer.book
        worksheet = writer.sheets['排名分析']
        
        # 设置列宽
        worksheet.set_column('A:E', 12)
        
        # 添加标题格式
        header_format = workbook.add_format({
            'bold': True,
            'text_wrap': True,
            'valign': 'top',
            'fg_color': '#D7E4BC',
            'border': 1
        })
        
        # 应用标题格式
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)
            
        # 添加条件格式（环比变化 - 对于排名，上升为好，所以负值用绿色）
        positive_format = workbook.add_format({'font_color': 'red'})
        negative_format = workbook.add_format({'font_color': 'green'})
        
        # 应用条件格式到校次环比变化（如果存在）
        if '校次环比变化' in df.columns:
            col_idx = df.columns.get_loc('校次环比变化')
            worksheet.conditional_format(1, col_idx, len(df), col_idx, {
                'type': 'cell',
                'criteria': '>',
                'value': 0,
                'format': positive_format
            })
            
            worksheet.conditional_format(1, col_idx, len(df), col_idx, {
                'type': 'cell',
                'criteria': '<',
                'value': 0,
                'format': negative_format
            })
        
        # 应用条件格式到班次环比变化（如果存在）
        if '班次环比变化' in df.columns:
            col_idx = df.columns.get_loc('班次环比变化')
            worksheet.conditional_format(1, col_idx, len(df), col_idx, {
                'type': 'cell',
                'criteria': '>',
                'value': 0,
                'format': positive_format
            })
            
            worksheet.conditional_format(1, col_idx, len(df), col_idx, {
                'type': 'cell',
                'criteria': '<',
                'value': 0,
                'format': negative_format
            })
    
    def _generate_trend_charts(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成成绩趋势图表"""
        # 获取总分数据
        total_scores = data['成绩趋势']['总分走势']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams']:
            return
            
        exam_names = [exam.get('display_name', exam.get('exam_type', f'考试{i+1}')) 
                      for i, exam in enumerate(exam_data['exams'])]
        
        # 过滤掉None值
        valid_scores = [(i, score) for i, score in enumerate(total_scores) if score is not None]
        
        if not valid_scores:
            return
            
        valid_indices = [i for i, _ in valid_scores]
        valid_scores = [score for _, score in valid_scores]
        valid_exam_names = [exam_names[i] for i in valid_indices]
        
        # 反转数据顺序，使其从远到近（时间轴从左到右）
        valid_scores_reversed = valid_scores[::-1]
        valid_exam_names_reversed = valid_exam_names[::-1]
        
        # 创建图表工作表
        workbook = writer.book
        worksheet = workbook.add_worksheet('成绩趋势图')
        
        # 写入数据（使用反转后的数据）
        worksheet.write_column('A1', ['考试'] + valid_exam_names_reversed)
        worksheet.write_column('B1', ['总分'] + valid_scores_reversed)
        
        # 创建图表
        chart = workbook.add_chart({'type': 'line'})
        
        # 添加数据系列
        chart.add_series({
            'name': '总分',
            'categories': ['成绩趋势图', 1, 0, len(valid_exam_names_reversed), 0],
            'values': ['成绩趋势图', 1, 1, len(valid_scores_reversed), 1],
            'marker': {'type': 'circle', 'size': 8},
            'data_labels': {'value': True}
        })
        
        # 设置图表标题和轴标签
        chart.set_title({'name': '总分成绩趋势'})
        chart.set_x_axis({'name': '考试'})
        chart.set_y_axis({'name': '分数'})
        
        # 插入图表
        worksheet.insert_chart('D1', chart, {'x_scale': 1.5, 'y_scale': 1.5})
    
    def _generate_subject_comparison(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成科目对比图表"""
        # 获取各科目数据
        subjects_data = data['成绩趋势']['各科走势']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams'] or not subjects_data:
            return
            
        exam_names = [exam.get('display_name', exam.get('exam_type', f'考试{i+1}')) 
                      for i, exam in enumerate(exam_data['exams'])]
        
        # 创建图表工作表
        workbook = writer.book
        worksheet = workbook.add_worksheet('科目对比图')
        
        # 找出所有科目中的有效数据
        valid_data = {}
        common_indices = set(range(len(exam_names)))
        
        for subject, scores in subjects_data.items():
            valid_indices = [i for i, score in enumerate(scores) if score is not None]
            if valid_indices:
                valid_data[subject] = [(i, scores[i]) for i in valid_indices]
                common_indices = common_indices.intersection(valid_indices)
        
        if not valid_data:
            return
            
        # 如果没有所有科目都有的考试，使用所有有效考试
        if not common_indices:
            all_valid_indices = set()
            for subject_data in valid_data.values():
                all_valid_indices.update([i for i, _ in subject_data])
            common_indices = sorted(all_valid_indices)
        else:
            common_indices = sorted(common_indices)
            
        if not common_indices:
            return
            
        # 获取共同考试的名称
        common_exam_names = [exam_names[i] for i in common_indices]
        
        # 反转顺序，使其从远到近（时间轴从左到右）
        common_indices_reversed = common_indices[::-1]
        common_exam_names_reversed = common_exam_names[::-1]
        
        # 写入数据（使用反转后的数据）
        worksheet.write_column('A1', ['考试'] + common_exam_names_reversed)
        
        col = 1
        for subject, data_points in valid_data.items():
            # 过滤出共同考试的成绩，并反转顺序
            subject_scores = []
            for idx in common_indices_reversed:  # 使用反转后的索引
                score = next((score for i, score in data_points if i == idx), None)
                subject_scores.append(score if score is not None else 0)
                
            if not subject_scores:
                continue
                
            # 写入科目数据
            col_letter = chr(65 + col)  # A, B, C, ...
            worksheet.write_column(f'{col_letter}1', [subject] + subject_scores)
            col += 1
        
        if col <= 1:
            return
            
        # 创建图表
        chart = workbook.add_chart({'type': 'line'})
        
        # 添加数据系列
        col = 1
        for subject in valid_data.keys():
            chart.add_series({
                'name': subject,
                'categories': ['科目对比图', 1, 0, len(common_exam_names_reversed), 0],
                'values': ['科目对比图', 1, col, len(common_exam_names_reversed), col],
                'marker': {'type': 'circle', 'size': 6}
            })
            col += 1
        
        # 设置图表标题和轴标签
        chart.set_title({'name': '各科目成绩对比'})
        chart.set_x_axis({'name': '考试'})
        chart.set_y_axis({'name': '分数'})
        
        # 插入图表
        worksheet.insert_chart('D1', chart, {'x_scale': 1.5, 'y_scale': 1.5})
    
    def _generate_ranking_charts(self, writer: pd.ExcelWriter, data: Dict, exam_data: Dict):
        """生成排名趋势图表"""
        # 获取排名数据
        rankings = data['排名分析']
        
        # 确保exam_data中有exams键
        if 'exams' not in exam_data or not exam_data['exams'] or not rankings:
            return
            
        exam_names = [exam.get('display_name', exam.get('exam_type', f'考试{i+1}')) 
                      for i, exam in enumerate(exam_data['exams'])]
        
        # 检查是否有有效的排名数据
        valid_rankings = {}
        for rank_type, rank_data in rankings.items():
            if rank_type not in ['校次', '班次']:
                continue
                
            ranks = rank_data.get('数据', [])
            valid_indices = [i for i, rank in enumerate(ranks) if rank is not None]
            
            if valid_indices:
                valid_rankings[rank_type] = [(i, ranks[i]) for i in valid_indices]
        
        if not valid_rankings:
            return
            
        # 找出所有排名类型中的共同考试
        common_indices = set(range(len(exam_names)))
        for rank_data in valid_rankings.values():
            valid_indices = [i for i, _ in rank_data]
            common_indices = common_indices.intersection(valid_indices)
        
        # 如果没有所有排名类型都有的考试，使用所有有效考试
        if not common_indices:
            all_valid_indices = set()
            for rank_data in valid_rankings.values():
                all_valid_indices.update([i for i, _ in rank_data])
            common_indices = sorted(all_valid_indices)
        else:
            common_indices = sorted(common_indices)
            
        if not common_indices:
            return
            
        # 获取共同考试的名称
        common_exam_names = [exam_names[i] for i in common_indices]
        
        # 反转顺序，使其从远到近（时间轴从左到右）
        common_indices_reversed = common_indices[::-1]
        common_exam_names_reversed = common_exam_names[::-1]
        
        # 创建图表工作表
        workbook = writer.book
        worksheet = workbook.add_worksheet('排名趋势图')
        
        # 写入数据（使用反转后的数据）
        worksheet.write_column('A1', ['考试'] + common_exam_names_reversed)
        
        col = 1
        for rank_type, data_points in valid_rankings.items():
            # 过滤出共同考试的排名，并反转顺序
            rank_values = []
            for idx in common_indices_reversed:  # 使用反转后的索引
                rank = next((rank for i, rank in data_points if i == idx), None)
                rank_values.append(rank if rank is not None else 0)
                
            if not rank_values:
                continue
                
            # 写入排名数据
            col_letter = chr(65 + col)  # A, B, C, ...
            worksheet.write_column(f'{col_letter}1', [rank_type] + rank_values)
            col += 1
        
        if col <= 1:
            return
            
        # 创建图表
        chart = workbook.add_chart({'type': 'line'})
        
        # 添加数据系列
        col = 1
        for rank_type in valid_rankings.keys():
            chart.add_series({
                'name': rank_type,
                'categories': ['排名趋势图', 1, 0, len(common_exam_names_reversed), 0],
                'values': ['排名趋势图', 1, col, len(common_exam_names_reversed), col],
                'marker': {'type': 'circle', 'size': 6},
                'data_labels': {'value': True}
            })
            col += 1
        
        # 设置图表标题和轴标签
        chart.set_title({'name': '排名趋势'})
        chart.set_x_axis({'name': '考试'})
        chart.set_y_axis({
            'name': '排名',
            'reverse': True  # 排名越小越好，所以反转Y轴
        })
        
        # 插入图表
        worksheet.insert_chart('D1', chart, {'x_scale': 1.5, 'y_scale': 1.5}) 